﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace HSPCS.Data
{
    public class Connector
    {
        /// <summary>
        /// Sql connection to database
        /// </summary>
        private SqlConnection connection;
        private SqlTransaction transaction;

        /// <summary>
        /// Initializes a new instance of the Connector class.
        /// </summary>
        public Connector()
        {
            string sql = @"Data Source=EDNAWS_2009;Initial Catalog=HansonPCS;Integrated Security=True";
            this.connection = new SqlConnection(sql);
        }

        

        /// <summary>
        /// Open a connection to SQL database
        /// </summary>
        /// <returns>True if Connection is opened successfully, otherwise false</returns>
        private bool OpenConnection()
        {
            try
            {
                this.connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Close a connection to SQL database
        /// </summary>
        private void CloseConnection()
        {
            try
            {
                if (this.connection != null && this.connection.State != ConnectionState.Closed)
                {
                    this.connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get data using ExecuteScalar
        /// </summary>
        /// <param name="resultData">return a string</param>
        /// <param name="query">a query statement</param>
        public void ExecuteScalar(ref object resultData, string query, SqlParameter[] sqlParameters = null)
        {
            try
            {
                if (this.OpenConnection())
                {
                    SqlCommand cmd = new SqlCommand(query, connection);
                    cmd.CommandType = CommandType.Text;

                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParam in sqlParameters)
                        {
                            cmd.Parameters.Add(sqlParam);
                        }
                    }

                    resultData = cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        /// <summary>
        /// get data from database with parameters
        /// </summary>
        /// <param name="resultData">Return datatable if have</param>
        /// <param name="query">A query statement</param>
        /// <param name="sqlParameters">List of parameter are passed into a query statement </param>
        public void ExecuteReader(ref DataTable resultData, string query, SqlParameter[] sqlParameters = null)
        {
            try
            {
                if (this.OpenConnection())
                {
                    SqlCommand cmd = new SqlCommand(query, connection);

                    // Add parameters if they are not null
                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParam in sqlParameters)
                        {
                            cmd.Parameters.Add(sqlParam);
                        }
                    }

                    SqlDataReader reader = cmd.ExecuteReader();
                    resultData = new DataTable();
                    resultData.Load(reader);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// Update data to database with parameters
        /// </summary>
        /// <param name="query">A none-query statement</param>
        /// <param name="sqlParameters">List of parameters of non-query statement</param>
        public int ExecuteNonQuery(string query, SqlParameter[] sqlParameters = null)
        {
            try
            {
                if (this.OpenConnection())
                {
                    //create command and assign the query and connection from the constructor
                    SqlCommand cmd = new SqlCommand(query, connection);
                    // set CommandType is Text
                    cmd.CommandType = System.Data.CommandType.Text;

                    // Add parameters if they are not null
                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParam in sqlParameters)
                        {
                            cmd.Parameters.Add(sqlParam);
                        }
                    }
                    // Execute non-query statement
                    return cmd.ExecuteNonQuery();
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }
        public SqlDataAdapter GetTablebyDataAdapter(string selectQuery, SqlParameter[] sqlParameters = null)
        {
            SqlDataAdapter dataAdapter;
            try
            {
                if (this.OpenConnection())
                {
                    //create command and assign the query and connection from the constructor
                    SqlCommand cmd = new SqlCommand(selectQuery, connection);
                    // set CommandType is Text
                    cmd.CommandType = System.Data.CommandType.Text;

                    // Add parameters if they are not null
                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParam in sqlParameters)
                        {
                            cmd.Parameters.Add(sqlParam);
                        }
                    }
                    //create Data Adapter and assign the query and connection from the constructor
                    dataAdapter = new SqlDataAdapter(cmd);
                    return dataAdapter;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

    public DataSet GetTablebyProcedure(string procedureName, SqlParameter[] sqlParameters = null)
        {
            SqlDataAdapter dataAdapter;
            try
            {
                if (this.OpenConnection())
                {
                    //create command and assign the query and connection from the constructor
                    SqlCommand cmd = new SqlCommand(procedureName, connection);
                    // set CommandType is Text
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    // Add parameters if they are not null
                    if (sqlParameters != null)
                    {
                        foreach (SqlParameter sqlParam in sqlParameters)
                        {

                            cmd.Parameters.Add(sqlParam);
                        }
                    }
                    //create Data Adapter and assign the query and connection from the constructor
                    dataAdapter = new SqlDataAdapter(cmd);
                    DataSet dataSet = new DataSet();
                    dataAdapter.Fill(dataSet);
                    return dataSet;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }
    }
    
}